from helpers.director.shortcut import page_dc,PlainTable,RowFilter,ModelTable,TrivalPageNum,director
from . models import QualityEvent,DepartMent
from django.db.models import Count,Case,When,Sum,Value,IntegerField,Q,F
from django.db.models.functions import Coalesce
from helpers.director.access.permit import has_permit
from django.core.exceptions import PermissionDenied

class StatisEventPage(object):
    def __init__(self, request,engin):
        self.request = request
        self.crt_user = request.user
    
    def get_label(self):
        return '质量事件分析'
    
    def check_permit(self):
        if not has_permit(self.crt_user,'quality_event_ansys'):
            raise PermissionDenied('不能查看图形分析界面')
        
    def get_template(self):
        return 'jb_admin/live.html'
    
    def get_context(self):
        return {
            'editor':'com-live-table-type',
            'editor_ctx':{
                 ** StatisEventTab().get_context(),
                 'inn_editor':'statisEvent',
                  
            }
        }
    
 


class StatisEventTab(ModelTable):
    model = QualityEvent
    exclude = []
    
    def get_rows(self):
        query = self.get_query()
        
        departs_query = {}
        dapart_out =[]
        for ii in DepartMent.objects.all():
            dapart_out.append({'pk':ii.pk,'label':ii.name})
            departs_query ['depart_%s_count'%ii.pk] = Coalesce( 
                             Sum( 
                                 Case(
                                     When(create_depart_id=ii.pk,then=1),
                                     default=0,
                                     output_field=IntegerField()
                                 )
                             ), 0)
        
        dc = query.exclude(total_count=0).aggregate(total_all=Count('id'),
                             close_count = Coalesce( 
                                 Sum( 
                                     Case(
                                         When(status=5,then=1),
                                         default=0,
                                         output_field=IntegerField()
                                     )
                                 ), 0),
                             jiejian_count = Coalesce( 
                                 Sum( 
                                     Case(
                                         When(status=3,then=1),
                                         default=0,
                                         output_field=IntegerField()
                                     )
                                 ), 0),
                             inn_count = Coalesce( 
                                 Sum( 
                                     Case(
                                         When(kind=1,then=1),
                                         default=0,
                                         output_field=IntegerField()
                                     )
                                 ), 0),   
                             
                             custormer_count = Coalesce( 
                                 Sum( 
                                     Case(
                                         When(kind=2,then=1),
                                         default=0,
                                         output_field=IntegerField()
                                     )
                                 ), 0),                               
                             
                             other_count = Coalesce( 
                                 Sum( 
                                     Case(
                                         When(kind=23,then=1),
                                         default=0,
                                         output_field=IntegerField()
                                     )
                                 ), 0),   
                             **departs_query,
                             
                             )
        
        
        dc['open_count'] = dc["total_all"]-dc['close_count']-dc['jiejian_count']
        dc['departs']=dapart_out
        
        # 产品不良率
        outlist= []
        for inst in  query.exclude(total_count=0).select_related('prod_record__model').all():
            outlist.append({
                'model_spec':inst.model_spec,
                'prod_no':inst.prod_no,
                'model_no':inst.prod_record.model.no if inst.prod_record else  '',
                'model_name':inst.prod_record.model.name if inst.prod_record else '',
                #'order_number':inst.prod_record.order_number if inst.prod_record else '',
                'fail_count':inst.fail_count,
                'total_count':inst.total_count,
            })
        dc['model_spec_ratio'],dc['model_spec_count'] = model_spec_ratio_order(outlist)
        dc['prod_no_ratio'],dc['prod_no_count'] = prod_count_order(outlist)
        
        # 简述 不良率
        
        return [
            dc
        ]
    
    pagenator = TrivalPageNum
    
    class filters(RowFilter):
        names = ['kind','status','create_depart','current_depart']
        range_fields =['create_time']
    
    #def getRowFilters(self):
        #return [
            #{'name':'bb','label':'哈哈','editor':'com-filter-select','options':[]}
        #]

def model_spec_ratio_order(outlist):
    "不良率汇总"
    ppobj ={}
    for row in outlist:
        model_spec = row['model_spec']
        if model_spec not in ppobj:
            ppobj[model_spec] = {'name':row['model_spec'],'fail_count':row['fail_count'],'total_count':row['total_count']}
        else:
            ppobj[model_spec]['fail_count'] += row['fail_count']
            ppobj[model_spec]['total_count'] += row['total_count']
        ppobj[model_spec]['model_no'] = row.get('model_no')
        ppobj[model_spec]['model_name'] = row.get('model_name')
        
    yylist = [v for k,v in ppobj.items()]
    for ii in yylist:
        ii['fail_ratio'] = ii['fail_count']/ii['total_count']
    y1list = sorted(yylist,key=lambda item:item['fail_ratio'],reverse=True)
    y2list = sorted(yylist,key=lambda item:item['fail_count'],reverse=True)
    for ii in yylist:
        ii ['fail_ratio'] = '%s%%'%round( ii ['fail_ratio'] *100,2)
    #for ii in y2list:
        #ii ['fail_ratio'] = '%s%%'%round( ii ['fail_ratio'] *100,2)      
    return y1list[:10],y2list[:10]

def prod_count_order(outlist):
    "生产批次汇总"
    ppobj ={}
    for row in outlist:
        prod_no = row['prod_no']
        if prod_no not in ppobj:
            ppobj[prod_no] = {'name':row['prod_no'],'fail_count':row['fail_count'],'total_count':row['total_count']}
        else:
            ppobj[prod_no]['fail_count'] += row['fail_count']
            ppobj[prod_no]['total_count'] += row['total_count']
        ppobj[prod_no]['model_no'] = row.get('model_no')
        ppobj[prod_no]['model_name'] = row.get('model_name')  
        ppobj[prod_no]['order_number'] = row.get('order_number')
        
    
    yylist = [v for k,v in ppobj.items()]
    for ii in yylist:
        ii['fail_ratio'] = ii['fail_count']/ii['total_count']    
    y1list = sorted(yylist,key=lambda item:item['fail_ratio'],reverse=True)
    y2list = sorted(yylist,key=lambda item:item['fail_count'],reverse=True)
    for ii in yylist:
        ii ['fail_ratio'] = '%s%%'%round( ii ['fail_ratio'] *100,2)
    #for ii in y2list:
        #ii ['fail_ratio'] = '%s%%'%round( ii ['fail_ratio'] *100,2)  
    return y1list[:10],y2list[:10]    

director.update({
    'statis_event.table':StatisEventTab
})

page_dc.update({
    'statis_event':StatisEventPage
})